import java.io.*;
import javax.servlet.http.*;
import javax.servlet.*;
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

public class Games extends HttpServlet
{

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

        String dbUser = "jh3240"; // enter your username here
        String dbPassword = "kuzifQbw"; // enter your password here

        int year, month, day;

        try
        {
            year = Integer.parseInt(request.getParameter("year"));
            month = Integer.parseInt(request.getParameter("month"));
            day = Integer.parseInt(request.getParameter("day"));
        }
        catch (NumberFormatException e)
        {
            out.println("Invalid date. Must be numbers!");
            return;
        }

        if (!isDateValid(year, month, day))
        {
            out.println("Invalid date. Date doesn't exist!");
            return;
        }

        try
        {
            OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
            ods.setURL("jdbc:oracle:thin:@//w4111g.cs.columbia.edu:1521/ADB");
            ods.setUser(dbUser);
            ods.setPassword(dbPassword);

            Connection conn = ods.getConnection();

            String query = new String();
            Statement s = conn.createStatement();

            String date = String.format("%d-%02d-%02d", year, month, day);

            query = "SELECT ht.name as hname, at.name as aname, at.tid as atid, ht.tid as htid, g.homescore, g.awayscore, g.gid "
                    + "FROM games g, teams ht, teams at "
                    + "WHERE ht.tid = g.hometeam and at.tid = g.awayteam and g.played_on = TO_DATE('"
                    + date
                    + "', 'yyyy-mm-dd')";

            ResultSet r = s.executeQuery(query);

            int i = 0;
            boolean hasGame = false;
            while (r.next())
            {
                hasGame = true;
                String scoreStr = r.getString("awayscore") + " to " + r.getString("homescore");
                String scoreLink = "<a href=\"/game?gid=" + r.getString("gid") + "\">" + scoreStr + "</a>";

                out.println("<a href=\"/team?teamid=" + r.getString("atid") + "&year=" + year + "\">" + r.getString("aname") + "</a>"
                        + " at " + "<a href=\"/team?teamid=" + r.getString("htid") + "&year=" + year + "\">" + r.getString("hname")
                        + "</a>" + " with score " + scoreLink + "<br />");
            }
            
            if (!hasGame)
                out.println("No game on this date!");
            
            r.close();
            s.close();

            conn.close();

        }
        catch (Exception e)
        {
            out.println("The database could not be accessed.<br>");
            out.println("More information is available as follows:<br>");
            e.printStackTrace(out);
        }

    } // end doGet method

    private static boolean isDateValid(int year, int month, int day)
    {
        if (year > 2011 || year < 2005)
            return false;

        if (month > 12 || month < 1)
            return false;

        if (day < 1)
            return false;

        if ((month == 1 || month == 3 || month == 5 || month == 7 || month == 8 || month == 10 || month == 12))
        {
            if (day > 31)
                return false;
        }
        else if (month == 2)
        {
            if (year % 4 == 0)
            {
                if (day > 29)
                    return false;
            }
            else
            {
                if (day > 28)
                    return false;
            }
        }
        else if (day > 30)
            return false;

        return true;
    }

} // end DatabaseServlet class

